package xyz.zyt123.videowall.dao;

import xyz.zyt123.videowall.bean.ErrorMsg;
import xyz.zyt123.videowall.bean.ErrorMsgProperties;
import xyz.zyt123.videowall.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Dao {

    public static int save(ErrorMsg errorMsg) {
        String sql = "insert into error_msg(uuid,camera_id,image_path,data_time,video_time) value (?,?,?,?,?)";
        try (Connection conn = JDBCUtils.getConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            int i = 0;
            ps.setString(++i, errorMsg.getUuid());
            ps.setString(++i, errorMsg.getCameraId());
            ps.setString(++i, errorMsg.getImagePath());
            ps.setTimestamp(++i, new Timestamp(errorMsg.getDataTime().getTime()));
            ps.setInt(++i, errorMsg.getVideoTime());
            return ps.executeUpdate();
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return 0;
    }

    //定义一个方法，查询user表的数据将其封装为对象，然后装载集合，返回。
    public static List<ErrorMsgProperties> findAll() {
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        List<ErrorMsgProperties> list = null;
        try {
            conn = JDBCUtils.getConnection();
            //定义sql
            String sql = "SELECT uuid,camera_id,image_path,data_time,video_time FROM error_msg order by data_time desc";
            //获取执行sql的对象
            st = conn.prepareStatement(sql);
            //执行sql
            rs = st.executeQuery();
            ErrorMsgProperties bean = null;
            list = new ArrayList<>();
            while (rs.next()) {
                String uuid = rs.getString("uuid");
                String cameraId = rs.getString("camera_id");
                String imagePath = rs.getString("image_path");
                Date dataTime = rs.getTimestamp("data_time");
                int videoTime = rs.getInt("video_time");

                bean = new ErrorMsgProperties(uuid, cameraId, imagePath, formatter.format(dataTime), videoTime/60+":"+videoTime%60);
                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(rs, st, conn);
        }
        return list;
    }
}
